CREATE TABLE Channel (
	ID			INTEGER			PRIMARY KEY,
	Provider	GUID			NOT NULL,
	FolderID	INTEGER			NOT NULL,
	Settings	NTEXT
);

CREATE TABLE Folder (
	ID			INTEGER			PRIMARY KEY,
	ParentID	INTEGER			NOT NULL REFERENCES Folder(ID)
		ON UPDATE CASCADE
		ON DELETE CASCADE,
	Name		NVARCHAR(100)	not null,
	Settings	NTEXT
);

CREATE TABLE Config (
	Name		NVARCHAR(100)	NOT NULL PRIMARY KEY,
	Value		BINARY
);


CREATE TABLE Document (
	ID			INTEGER			PRIMARY KEY,
	FolderID	INTEGER			NOT NULL REFERENCES Folder(ID)
		ON UPDATE CASCADE
		ON DELETE CASCADE, 
	Type		NVARCHAR(100)	NOT NULL,
	Guid		GUID			NOT NULL,
	Version		BINARY			NOT NULL,
	TimeStamp	DATETIME		NOT NULL
);

CREATE TABLE FieldEntry (
	ID			INTEGER			PRIMARY KEY,
	DocumentID	INTEGER			NOT NULL REFERENCES Document(ID)
		ON UPDATE CASCADE
		ON DELETE CASCADE, 
	Name		NVARCHAR(100)	NOT NULL,
	Value		BINARY
);


SELECT   Folders.Guid, Folders.ParentGuid, Folders.Name, AllMessagesCount.Count AS TotalCount, 
                UnreadMessagesCount.Count AS UnreadCount, Folders.Settings
FROM      Folders LEFT OUTER JOIN
                    (SELECT   FolderGuid, COUNT(Guid) AS Count
                     FROM      Messages
                     GROUP BY FolderGuid) AS AllMessagesCount ON Folders.Guid = AllMessagesCount.FolderGuid LEFT OUTER JOIN
                    (SELECT   FolderGuid, COUNT(Guid) AS Count
                     FROM      Messages AS Messages_1
                     WHERE   ([Read] = 0)
                     GROUP BY FolderGuid) AS UnreadMessagesCount ON Folders.Guid = UnreadMessagesCount.FolderGuid







